import json
from controllers.config import STATIC_PATH

DOC_NUM = 21
LANG_LIST = [str(i + 1) for i in range(21)]

def ToInt(value):
    return int(float(value))

def recharge_rebate_project_doc(excel_data, db):
    excel_result = {}
    info_tbl = excel_data.sheet_by_name("info")
    task_tbl = excel_data.sheet_by_name("task")
    for r_idx in range(1, info_tbl.nrows):
        pid = str(int(info_tbl.cell(r_idx, 1).value))
        if pid not in excel_result:
            excel_result[pid] = {}
        for i, doc_id in enumerate(LANG_LIST):
            title = str(info_tbl.cell(r_idx, 3+i).value)
            info = str(info_tbl.cell(r_idx, 3+i+DOC_NUM).value)
            content = str(info_tbl.cell(r_idx, 3+i+DOC_NUM*2).value)

            if doc_id not in excel_result[pid]:
                excel_result[pid][doc_id] = {}
            excel_result[pid][doc_id]['title'] = title
            excel_result[pid][doc_id]['info'] = info
            excel_result[pid][doc_id]['content'] = content

    for r_idx in range(1, task_tbl.nrows):
        pid = str(int(task_tbl.cell(r_idx, 0).value))
        idx = str(int(task_tbl.cell(r_idx, 1).value))
        if pid not in excel_result:
            excel_result[pid] = {}
        for i, doc_id in enumerate(LANG_LIST):
            task_doc = str(task_tbl.cell(r_idx, 3+i).value)
            if 'task_info' not in excel_result[pid][doc_id]:
                excel_result[pid][doc_id]['task_info'] = {}

            excel_result[pid][doc_id]['task_info'][idx] = task_doc

    query = db.query.all()
    for que in query:
        project = str(que.project)
        if project in excel_result:
            localization = excel_result[project]
            que.localization = json.dumps(localization)

def continuous_activities_task_content_doc(excel_data, path):
    result = {}
    task_tbl = excel_data.sheet_by_name('task')
    detail_tbl = excel_data.sheet_by_name('detail_id')
    task_content = {}
    detail_content = {}

    for r_idx in range(1, task_tbl.nrows):
        task_type = str(ToInt(task_tbl.cell(r_idx, 0).value))
        name = str(task_tbl.cell(r_idx, 1).value)
        # remark = str(task_tbl.cell(r_idx, 2).value)
        detail_type_raw = task_tbl.cell(r_idx, 3).value
        detail_type = '' if detail_type_raw == '/' else ToInt(detail_type_raw)
        op_type = int(task_tbl.cell(r_idx, 5).value)
        time_flag = False if ToInt(task_tbl.cell(r_idx, 4).value) == 0 else True
        first_doc = str(task_tbl.cell(r_idx, 6).value)
        id_flag = True if 'STRING2' in first_doc else False
        level_flag = True if 'STRING1' in first_doc else False
        task_doc = {}
        if first_doc:
            for i in range(DOC_NUM):
                lang_id = str(i+1)
                task_doc[lang_id] = str(task_tbl.cell(r_idx, 6+i).value)

        task_content[task_type] = {'name': name,
                                   'detail_type': detail_type,
                                   'id_flag': id_flag,
                                   'level_flag':level_flag,
                                   'time_flag': time_flag,
                                   'op_type': op_type,
                                   'task_doc': task_doc}

    for r_idx in range(1, detail_tbl.nrows):
        detail_type = str(ToInt(detail_tbl.cell(r_idx, 1).value))
        # 转盘
        if detail_type == '10':
            continue

        _id = ToInt(detail_tbl.cell(r_idx, 2).value)
        detail_doc= {}
        for i in range(DOC_NUM):
            if not detail_tbl.cell(r_idx, 3+i).value:
                break
            lang_id = str(i+1)
            detail_doc[lang_id] = str(detail_tbl.cell(r_idx, 3+i).value)

        if not detail_doc:
            detail_doc['1'] = 'Null'
        if detail_type not in detail_content:
            detail_content[detail_type] = []
        detail_content[detail_type].append({'id': _id, 'detail_doc': detail_doc})

    result['task_type'] = task_content
    result['detail_type'] = detail_content

    result_json = json.dumps(result, separators=(',', ':'))
    with open(path + 'continuous_activities_task_content.json', 'w', encoding='utf-8') as f:
        f.write(result_json)

def target_activity_task_content_doc(excel_data, path):
    result = {}
    task_tbl = excel_data.sheet_by_name('task')
    detail_tbl = excel_data.sheet_by_name('detail')
    task_content = {}
    detail_content = {}

    for r_idx in range(1, task_tbl.nrows):
        task_type = str(ToInt(task_tbl.cell(r_idx, 0).value))
        name = str(task_tbl.cell(r_idx, 1).value)
        # remark = str(task_tbl.cell(r_idx, 2).value)
        detail_type_raw = task_tbl.cell(r_idx, 3).value
        detail_type = '' if detail_type_raw == '/' else ToInt(detail_type_raw)
        op_type = int(task_tbl.cell(r_idx, 5).value)
        time_flag = False if ToInt(task_tbl.cell(r_idx, 4).value) == 0 else True

        first_doc = str(task_tbl.cell(r_idx, 6).value)
        id_flag = True if 'STRING2' in first_doc else False
        level_flag = True if 'STRING4' in first_doc else False
        task_doc = {}
        if first_doc:
            for i in range(DOC_NUM):
                lang_id = str(i+1)
                task_doc[lang_id] = str(task_tbl.cell(r_idx, 6+i).value)

        task_content[task_type] = {'name': name,
                                   'detail_type': detail_type,
                                   'id_flag': id_flag,
                                   'time_flag': time_flag,
                                   'level_flag':level_flag,
                                   'op_type': op_type,
                                   'task_doc': task_doc}

    for r_idx in range(1, detail_tbl.nrows):
        detail_type = str(ToInt(detail_tbl.cell(r_idx, 1).value))
        # 转盘
        if detail_type == '10':
            continue

        _id = ToInt(detail_tbl.cell(r_idx, 2).value)
        detail_doc= {}
        for i in range(DOC_NUM):
            if not detail_tbl.cell(r_idx, 3+i).value:
                break
            lang_id = str(i+1)
            detail_doc[lang_id] = str(detail_tbl.cell(r_idx, 3+i).value)

        if not detail_doc:
            detail_doc['1'] = 'Null'
        if detail_type not in detail_content:
            detail_content[detail_type] = []
        detail_content[detail_type].append({'id': _id, 'detail_doc': detail_doc})

    result['task_type'] = task_content
    result['detail_type'] = detail_content

    result_json = json.dumps(result, separators=(',', ':'))
    with open(path + 'target_task_content.json', 'w', encoding='utf-8') as f:
        f.write(result_json)

def continuous_activities_proj_doc(excel_data, db):
    excel_result = {}
    info_tbl = excel_data.sheet_by_name("info")
    theme_tbl = excel_data.sheet_by_name("theme")
    sub_theme_tbl = excel_data.sheet_by_name("sub_theme")

    for r_idx in range(1, info_tbl.nrows):
        pid = str(ToInt(info_tbl.cell(r_idx, 1).value))
        excel_result[pid] = {}
        for i, doc_id in enumerate(LANG_LIST):
            title = info_tbl.cell(r_idx, 3+i).value
            info = info_tbl.cell(r_idx, 3+i+DOC_NUM).value
            forecast_slogan = info_tbl.cell(r_idx, 3+i+DOC_NUM*2).value
            forecast_content = info_tbl.cell(r_idx, 3+i+DOC_NUM*3).value

            if doc_id not in excel_result[pid]:
                excel_result[pid][doc_id] = {}
            excel_result[pid][doc_id]['title'] = title
            excel_result[pid][doc_id]['info'] = info
            excel_result[pid][doc_id]['forecast_slogan'] = forecast_slogan
            excel_result[pid][doc_id]['forecast_content'] = forecast_content
            if 'task_group' not in excel_result[pid][doc_id]:
                excel_result[pid][doc_id]['task_group'] = []

    for pid in excel_result.keys():
        for r_idx in range(1, theme_tbl.nrows):
            theme_idx = int(theme_tbl.cell(r_idx, 0).value)
            for i, doc_id in enumerate(LANG_LIST):
                theme_doc = theme_tbl.cell(r_idx, 1+i).value

                task_group = excel_result[pid][doc_id]['task_group']
                if len(task_group) <= theme_idx:
                    task_group.append({'theme': theme_doc,
                                       'task': []})
                else:
                    task_group[theme_idx]['theme'] = theme_doc


        for r_idx in range(1, sub_theme_tbl.nrows):
            sub_theme_id_str = str(sub_theme_tbl.cell(r_idx, 0).value)
            sub_theme_idx_list = sub_theme_id_str.split('_')
            theme_idx = int(sub_theme_idx_list[0])
            sub_theme_idx = int(sub_theme_idx_list[1])

            for i, doc_id in enumerate(LANG_LIST):
                sub_theme_doc = sub_theme_tbl.cell(r_idx, 1+i).value

                task_group = excel_result[pid][doc_id]['task_group']
                if len(task_group[theme_idx]['task']) <= sub_theme_idx:
                    task_group[theme_idx]['task'].append({'sub_theme': sub_theme_doc,
                                               'task_item': []})
                else:
                    task_group[theme_idx]['task'][sub_theme_idx]['sub_theme'] = sub_theme_doc

    query = db.query.all()
    for que in query:
        project = str(que.project)
        if project in excel_result:
            localization = excel_result[project]
            que.localization = json.dumps(localization)

def novice_recharge_rebate_project_doc(excel_data, db):
    excel_result = {}
    info_tbl = excel_data.sheet_by_name("info")
    task_tbl = excel_data.sheet_by_name("task")
    for r_idx in range(1, info_tbl.nrows):
        pid = str(int(info_tbl.cell(r_idx, 1).value))
        if pid not in excel_result:
            excel_result[pid] = {}
        for i, doc_id in enumerate(LANG_LIST):
            title = str(info_tbl.cell(r_idx, 3+i).value)
            info = str(info_tbl.cell(r_idx, 3+i+DOC_NUM).value)
            content = str(info_tbl.cell(r_idx, 3+i+DOC_NUM*2).value)

            if doc_id not in excel_result[pid]:
                excel_result[pid][doc_id] = {}
            excel_result[pid][doc_id]['title'] = title
            excel_result[pid][doc_id]['info'] = info
            excel_result[pid][doc_id]['content'] = content

    for r_idx in range(1, task_tbl.nrows):
        pid = str(int(task_tbl.cell(r_idx, 0).value))
        idx = str(int(task_tbl.cell(r_idx, 1).value))
        if pid not in excel_result:
            excel_result[pid] = {}
        for i, doc_id in enumerate(LANG_LIST):
            task_doc = str(task_tbl.cell(r_idx, 3+i).value)
            if 'task_info' not in excel_result[pid][doc_id]:
                excel_result[pid][doc_id]['task_info'] = {}

            excel_result[pid][doc_id]['task_info'][idx] = task_doc

    query = db.query.all()
    for que in query:
        project = str(que.project)
        if project in excel_result:
            localization = excel_result[project]
            que.localization = json.dumps(localization)

def backflow_main_proj_doc(excel_data, db):
    main_result = {}
    login_result = {}
    consume_result = {}
    task_result = {}
    iap_result = {}
    task_doc_result = {}
    day_doc_result = {}
    migration_result = {}

    main_tbl = excel_data.sheet_by_name("main")
    login_tbl = excel_data.sheet_by_name("login")
    consume_tbl = excel_data.sheet_by_name("consume")
    consume_group_tbl = excel_data.sheet_by_name("consume_group")
    task_tbl = excel_data.sheet_by_name("task")
    iap_tbl = excel_data.sheet_by_name("iap")
    task_doc_tbl = excel_data.sheet_by_name("task_doc")
    day_doc_tbl = excel_data.sheet_by_name("day_doc")
    migration_tbl = excel_data.sheet_by_name("migration")

    for r_idx in range(1, main_tbl.nrows):
        for i, doc_id in enumerate(LANG_LIST):
            title_doc = str(main_tbl.cell(r_idx, i).value)
            if doc_id not in main_result:
                main_result[doc_id] = {}
            main_result[doc_id]['title'] = title_doc

    for r_idx in range(1, login_tbl.nrows):
        for i, doc_id in enumerate(LANG_LIST):
            title_doc = str(login_tbl.cell(r_idx, i).value)
            sub_title_doc = str(login_tbl.cell(r_idx, i+DOC_NUM).value)
            info_doc = str(login_tbl.cell(r_idx, i+DOC_NUM*2).value)
            if doc_id not in login_result:
                login_result[doc_id] = {}
            login_result[doc_id]['title'] = title_doc
            login_result[doc_id]['sub_title'] = sub_title_doc
            login_result[doc_id]['info'] = info_doc

    for r_idx in range(1, consume_tbl.nrows):
        for i, doc_id in enumerate(LANG_LIST):
            title_doc = str(consume_tbl.cell(r_idx, i).value)
            info_doc = str(consume_tbl.cell(r_idx, i+DOC_NUM).value)
            if doc_id not in consume_result:
                consume_result[doc_id] = {}
            consume_result[doc_id]['title'] = title_doc
            consume_result[doc_id]['info'] = info_doc

    for r_idx in range(1, consume_group_tbl.nrows):
        for i, doc_id in enumerate(LANG_LIST):
            title_doc = str(consume_group_tbl.cell(r_idx, 1+i).value)
            info_doc = str(consume_group_tbl.cell(r_idx, 1+i+DOC_NUM).value)

            if 'group_title' not in consume_result[doc_id]:
                consume_result[doc_id]['group_title'] = []
            consume_result[doc_id]['group_title'].append(
                {'title': title_doc, 'info': info_doc})

    for r_idx in range(1, task_tbl.nrows):
        for i, doc_id in enumerate(LANG_LIST):
            title_doc = str(task_tbl.cell(r_idx, i).value)
            info_doc = str(task_tbl.cell(r_idx, i+DOC_NUM).value)

            if doc_id not in task_result:
                task_result[doc_id] = {}
            task_result[doc_id]['title'] = title_doc
            task_result[doc_id]['info'] = info_doc

    for r_idx in range(1, iap_tbl.nrows):
        for i, doc_id in enumerate(LANG_LIST):
            title_doc = str(iap_tbl.cell(r_idx, i).value)
            info_doc = str(iap_tbl.cell(r_idx, i+DOC_NUM).value)

            if doc_id not in iap_result:
                iap_result[doc_id] = {}
            iap_result[doc_id]['title'] = title_doc
            iap_result[doc_id]['info'] = info_doc

    for r_idx in range(1, migration_tbl.nrows):
        for i, doc_id in enumerate(LANG_LIST):
            title_doc = str(migration_tbl.cell(r_idx, i).value)
            sub_title_doc = str(migration_tbl.cell(r_idx, i+DOC_NUM).value)
            info_doc = str(migration_tbl.cell(r_idx, i+DOC_NUM*2).value)

            if doc_id not in migration_result:
                migration_result[doc_id] = {}
            migration_result[doc_id]['title'] = title_doc
            migration_result[doc_id]['sub_title'] = sub_title_doc
            migration_result[doc_id]['info'] = info_doc

    cur_name = ''
    cur_name_key = ''
    day_count = 1
    for r_idx in range(1, task_doc_tbl.nrows):
        # result = {
        #     'login': {'$task_type': $doc_id},
        #     'task': {'$task_type': $doc_id},
        #     'consume': {'$task_type': $doc_id}
        # }
        name = task_doc_tbl.cell(r_idx, 0).value
        if not name:
            name = cur_name
        else:
            name = str(task_doc_tbl.cell(r_idx, 0).value).strip()
            cur_name = name
        if name == '登录活动':
            if 'login' not in task_doc_result:
                task_doc_result['login'] = {}
            cur_name_key = 'login'
        elif name == '回归任务':
            if 'task' not in task_doc_result:
                task_doc_result['task'] = {}
            cur_name_key = 'task'
        elif name == '消耗活动':
            if 'consume' not in task_doc_result:
                task_doc_result['consume'] = {}
            cur_name_key = 'consume'

        task_type = str(ToInt(task_doc_tbl.cell(r_idx, 3).value))
        doc_id = int(task_doc_tbl.cell(r_idx, 2).value)
        if cur_name_key == 'login':
            task_doc_result[cur_name_key][str(day_count)] = doc_id
            day_count += 1
        else:
            task_doc_result[cur_name_key][task_type] = doc_id

    for r_idx in range(1, day_doc_tbl.nrows):
        # result =
        # {
        #     '$天数':
        #     {
        #         '$lang_id': $day_doc
        #     }
        # }
        cur_day = str(r_idx-1)
        if cur_day not in day_doc_result:
            day_doc_result[cur_day] = {}
        for i, doc_id in enumerate(LANG_LIST):
            day_doc = str(day_doc_tbl.cell(r_idx, 2+i).value)
            day_doc_result[cur_day][doc_id] = day_doc

    query = db.query.all()
    for que in query:
        que.main_localization = json.dumps(main_result)
        que.login_localization = json.dumps(login_result)
        que.consume_localization = json.dumps(consume_result)
        que.task_localization = json.dumps(task_result)
        que.iap_localization = json.dumps(iap_result)
        que.task_doc_localization = json.dumps(task_doc_result)
        que.day_doc_localization = json.dumps(day_doc_result)
        que.migration_localization = json.dumps(migration_result)

def score_theme_battle_pass_project_doc(excel_data, db):
    excel_result = {}
    info_tbl = excel_data.sheet_by_name("info")
    for r_idx in range(1, info_tbl.nrows):
        pid = str(int(info_tbl.cell(r_idx, 1).value))
        if pid not in excel_result:
            excel_result[pid] = {}

        title = int(info_tbl.cell(r_idx, 3).value)
        tab_title = int(info_tbl.cell(r_idx, 4).value)
        info = int(info_tbl.cell(r_idx, 5).value)

        excel_result[pid]['title'] = title
        excel_result[pid]['tab_title'] = tab_title
        excel_result[pid]['info'] = info

    query = db.query.all()
    for que in query:
        project = str(que.project)
        if project in excel_result:
            localization = excel_result[project]
            que.localization = json.dumps(localization)

def task_theme_battle_pass_project_doc(excel_data, db):
    excel_result = {}
    info_tbl = excel_data.sheet_by_name("info")
    for r_idx in range(1, info_tbl.nrows):
        pid = str(int(info_tbl.cell(r_idx, 1).value))
        if pid not in excel_result:
            excel_result[pid] = {}

        title = int(info_tbl.cell(r_idx, 3).value)
        tab_title = int(info_tbl.cell(r_idx, 4).value)
        info = int(info_tbl.cell(r_idx, 5).value)

        excel_result[pid]['title'] = title
        excel_result[pid]['tab_title'] = tab_title
        excel_result[pid]['info'] = info

    query = db.query.all()
    for que in query:
        project = str(que.project)
        if project in excel_result:
            localization = excel_result[project]
            que.localization = json.dumps(localization)

def task_theme_bp_task_content_doc(excel_data):
    excel_result = {}
    info_tbl = excel_data.sheet_by_name("info")
    for r_idx in range(1, info_tbl.nrows):
        task_id = str(int(info_tbl.cell(r_idx, 1).value))
        doc_id = int(info_tbl.cell(r_idx, 2).value)
        excel_result[task_id] = doc_id
    with open(STATIC_PATH + 'task_theme_bp_task_doc.json', 'w', encoding='utf-8') as f:
        f.write(json.dumps(excel_result))

def score_theme_bp_task_content_doc(excel_data):
    excel_result = {}
    info_tbl = excel_data.sheet_by_name("info")
    for r_idx in range(1, info_tbl.nrows):
        task_id = str(int(info_tbl.cell(r_idx, 1).value))
        doc_id = int(info_tbl.cell(r_idx, 2).value)
        excel_result[task_id] = doc_id
    with open(STATIC_PATH + 'score_theme_bp_task_doc.json', 'w', encoding='utf-8') as f:
        f.write(json.dumps(excel_result))

def store_treasure_project_doc(excel_data, db):
    excel_result = {}
    info_tbl = excel_data.sheet_by_name("info")
    for r_idx in range(1, info_tbl.nrows):
        pid = str(int(info_tbl.cell(r_idx, 1).value))
        if pid not in excel_result:
            excel_result[pid] = {}
        for i, doc_id in enumerate(LANG_LIST):
            title = str(info_tbl.cell(r_idx, 3+i).value)
            event_title = str(info_tbl.cell(r_idx, 3+i+DOC_NUM).value)
            info = str(info_tbl.cell(r_idx, 3+i+DOC_NUM*2).value)
            if doc_id not in excel_result[pid]:
                excel_result[pid][doc_id] = {}
            excel_result[pid][doc_id]['title'] = title
            excel_result[pid][doc_id]['event_title'] = event_title
            excel_result[pid][doc_id]['info'] = info

    query = db.query.all()
    for que in query:
        project = str(que.project)
        if project in excel_result:
            localization = excel_result[project]
            que.localization = json.dumps(localization)

def gold_rush_project_doc(excel_data, db):
    excel_result = {}
    info_tbl = excel_data.sheet_by_name("info")
    for r_idx in range(1, info_tbl.nrows):
        pid = str(int(info_tbl.cell(r_idx, 1).value))
        if pid not in excel_result:
            excel_result[pid] = {}
        for i, doc_id in enumerate(LANG_LIST):
            title = int(info_tbl.cell(r_idx, 3+i).value)
            event_title = int(info_tbl.cell(r_idx, 3+i+DOC_NUM).value)
            info = int(info_tbl.cell(r_idx, 3+i+DOC_NUM*2).value)
            if doc_id not in excel_result[pid]:
                excel_result[pid][doc_id] = {}
            excel_result[pid][doc_id]['title'] = title
            excel_result[pid][doc_id]['event_title'] = event_title
            excel_result[pid][doc_id]['info'] = info

    query = db.query.all()
    for que in query:
        project = str(que.project)
        if project in excel_result:
            localization = excel_result[project]
            que.localization = json.dumps(localization)

def gold_rush_task_doc(excel_data, db):
    excel_result = {}
    sheets = excel_data.sheet_names()
    for sheet in sheets:
        tbl = excel_data.sheet_by_name(sheet)
        pid = sheet[2:].strip()
        if pid not in excel_result:
            excel_result[pid] = {}
        task_id = str(int(tbl.cell(r_idx, 1).value))
        doc_id = int(tbl.cell(r_idx, 2).value)

def shop_project_doc(excel_data, db):
    excel_result = {}
    info_tbl = excel_data.sheet_by_name("info")
    for r_idx in range(1, info_tbl.nrows):
        pid = str(int(info_tbl.cell(r_idx, 1).value))
        if pid not in excel_result:
            excel_result[pid] = {}
        for i in range(DOC_NUM):
            title = str(info_tbl.cell(r_idx, 2+i).value)
            excel_result[pid][str(i)] = title

    query = db.query.all()
    for que in query:
        project = str(que.project)
        if project in excel_result:
            localization = excel_result[project]
            que.title = json.dumps(localization)


def western_shadow_menu_project_doc(excel_data, db):
    menu_result = {}
    record_result = {}
    xiaoyong_result = {}
    hospital_result = {}
    task_doc = {} # {event_type: {lv: doc_id}}
    menu_tbl = excel_data.sheet_by_name("menu")
    record_tbl = excel_data.sheet_by_name("record")
    xiaoyong_tbl = excel_data.sheet_by_name("xiaoyong")
    hospital_tbl = excel_data.sheet_by_name("hospital")
    task_tbl = excel_data.sheet_by_name("task_doc")
    for r_idx in range(1, menu_tbl.nrows):
        pid = str(int(menu_tbl.cell(r_idx, 1).value))
        if pid not in menu_result:
            menu_result[pid] = {}
        title = int(menu_tbl.cell(r_idx, 3).value)
        info = int(menu_tbl.cell(r_idx, 4).value)
        menu_result[pid] = {'title': title, 'info': info}

    for r_idx in range(1, record_tbl.nrows):
        pid = str(int(record_tbl.cell(r_idx, 1).value))
        if pid not in record_result:
            record_result[pid] = {'1': {}}

        title = int(record_tbl.cell(r_idx, 3).value)
        info = int(record_tbl.cell(r_idx, 4).value)
        content = int(record_tbl.cell(r_idx, 5).value)
        record_result[pid]['1'] = {'title': title,
                                   'info': info,
                                   'content': content}

    for r_idx in range(1, xiaoyong_tbl.nrows):
        pid = str(int(xiaoyong_tbl.cell(r_idx, 1).value))
        if pid not in xiaoyong_result:
            xiaoyong_result[pid] = {'1': {}}

        title = int(xiaoyong_tbl.cell(r_idx, 3).value)
        info = int(xiaoyong_tbl.cell(r_idx, 4).value)
        content = int(xiaoyong_tbl.cell(r_idx, 5).value)
        xiaoyong_result[pid]['1'] = {'title': title,
                                     'info': info,
                                     'content': content}

    for r_idx in range(1, hospital_tbl.nrows):
        pid = str(int(hospital_tbl.cell(r_idx, 1).value))
        if pid not in hospital_result:
            hospital_result[pid] = {}
        title = int(hospital_tbl.cell(r_idx, 3).value)
        info = int(hospital_tbl.cell(r_idx, 4).value)
        content = int(hospital_tbl.cell(r_idx, 5).value)
        hospital_result[pid] = {'title': title,
                                'info': info,
                                'content': content}

    cur_event_type = str(int(task_tbl.cell(1, 0).value))
    for r_idx in range(1, task_tbl.nrows):
        event_type = task_tbl.cell(r_idx, 0).value
        if event_type:
            event_type_str = str(int(event_type))
            cur_event_type = event_type_str
        lv = str(int(task_tbl.cell(r_idx, 1).value))
        doc_id = int(task_tbl.cell(r_idx, 2).value)

        if cur_event_type not in task_doc:
            task_doc[cur_event_type] = {}
        task_doc[cur_event_type][lv] = doc_id

    with open(STATIC_PATH+'western_shadow_record_doc_id.json', 'w', encoding='utf-8') as f:
        f.write(json.dumps(task_doc))

    query = db.query.all()
    for que in query:
        project = str(que.project)
        localization = json.loads(que.localization)
        updated = False
        if project in menu_result:
            localization['menu_loc'] = menu_result[project]
            updated = True
        if project in record_result:
            localization['record_loc'] = record_result[project]
            updated = True
        if project in xiaoyong_result:
            localization['xiaoyong_loc'] = xiaoyong_result[project]
            updated = True
        if project in hospital_result:
            localization['hospital_loc'] = hospital_result[project]
            updated = True
        if updated:
            que.localization = json.dumps(localization)


def duel_yard_event_proj_doc(excel_data, db):
    excel_result = {}
    info_tbl = excel_data.sheet_by_name("info")
    for r_idx in range(1, info_tbl.nrows):
        pid = str(int(info_tbl.cell(r_idx, 1).value))
        if pid not in excel_result:
            excel_result[pid] = {}
        for i in range(DOC_NUM):
            title = str(info_tbl.cell(r_idx, 2+i).value)
            excel_result[pid][str(i+1)] = title

    query = db.query.all()
    for que in query:
        project = str(que.project)
        if project in excel_result:
            localization = excel_result[project]
            que.title = json.dumps(localization)


def extreme_user_event_project_task(excel_data, extreme_db, db):
    excel_result = {}
    info_tbl = excel_data.sheet_by_name("task_pool")
    for r_idx in range(1, info_tbl.nrows):
        _id = ToInt(info_tbl.cell(r_idx, 0).value)
        label = str(info_tbl.cell(r_idx, 1).value)
        score_type = ToInt(info_tbl.cell(r_idx, 2).value)
        score_id = str(info_tbl.cell(r_idx, 3).value)
        age = ToInt(info_tbl.cell(r_idx, 4).value)
        doc_id = ToInt(info_tbl.cell(r_idx, 5).value)
        target_unit = ToInt(info_tbl.cell(r_idx, 6).value)

        score_map_id = {}
        if ',' in score_id:
            tmp_score_id = score_id.split(",")
            for x in tmp_score_id:
                score_map_id[str(x)] = 1
        else:
            score_map_id[str(ToInt(score_id))] = 1

        excel_result[str(_id)] = {'label': label, 'doc_id': doc_id,
                                  'score_type': score_type,
                                  'age': age,
                                  'score_map_id': score_map_id,
                                  'target_unit': target_unit}

    for k, v in excel_result.items():
        que = extreme_db.query.filter_by(id=int(k)).first()
        if que:
            que.data = json.dumps(v)
        else:
            new_node = extreme_db(id=int(k), data=json.dumps(v))
            db.session.add(new_node)

    return False


def kvk_event_project_doc(excel_data, db):
    new_result = {}
    old_result = {}
    new_tbl = excel_data.sheet_by_name('new_info')
    old_tbl = excel_data.sheet_by_name('old_info')
    for r_idx in range(1, new_tbl.nrows):
        pid = str(int(new_tbl.cell(r_idx, 0).value))
        if pid not in new_result:
            new_result[pid] = {}

        doc_id = int(new_tbl.cell(r_idx, 1).value)
        remark = str(new_tbl.cell(r_idx, 2).value)
        hospital_doc_id = int(new_tbl.cell(r_idx, 3).value)

        new_result[pid]['doc_id'] = doc_id
        new_result[pid]['remark'] = remark
        new_result[pid]['hospital_doc_id'] = hospital_doc_id

    for r_idx in range(1, old_tbl.nrows):
        pid = str(int(old_tbl.cell(r_idx, 0).value))
        if pid not in old_result:
            old_result[pid] = {}

        for i in range(DOC_NUM):
            title = str(old_tbl.cell(r_idx, 2+i).value)
            info = str(old_tbl.cell(r_idx, 2+i+DOC_NUM).value)
            content = str(old_tbl.cell(r_idx, 2+i+DOC_NUM*2).value)

            old_result[pid][str(i+1)] = {'title': title,
                                         'info': info,
                                         'content': content}

    query = db.query.all()
    for que in query:
        project = str(que.project)
        if project in new_result:
            localization = new_result[project]
            que.localization = json.dumps(localization)

        if project in old_result:
            old_localization = old_result[project]
            que.old_localization = json.dumps(old_localization)

def kvk_event_score_doc(excel_data):
    result = {}
    resource_result = {}
    tbl = excel_data.sheet_by_name('old_score')
    resource_tbl = excel_data.sheet_by_name('resourse')

    for r_idx in range(1, tbl.nrows):
        score_type = str(int(tbl.cell(r_idx, 0).value))
        if score_type not in result:
            result[score_type] = {}
        for i in range(DOC_NUM):
            score_content = str(tbl.cell(r_idx, 2+i).value)
            result[score_type][str(i+1)] = score_content

    for r_idx in range(1, resource_tbl.nrows):
        resource_id = int(resource_tbl.cell(r_idx, 0).value)
        if resource_id not in resource_result:
            resource_result[resource_id] = {}
        for i in range(DOC_NUM):
            resource_name = str(resource_tbl.cell(r_idx, 2+i).value)
            resource_result[resource_id][str(i+1)] = resource_name

    with open(STATIC_PATH + 'kvk_event_old_score_type.json', 'w', encoding='utf-8') as f:
        f.write(json.dumps(result))

    with open(STATIC_PATH + 'kvk_event_old_resource_name.json', 'w', encoding='utf-8') as f:
        f.write(json.dumps(resource_result))


def ava_event_project_doc(excel_data, db):
    excel_result = {}
    calendar_result = {}
    info_tbl = excel_data.sheet_by_name("info")
    for r_idx in range(1, info_tbl.nrows):
        pid = str(int(info_tbl.cell(r_idx, 1).value))
        if pid not in excel_result:
            excel_result[pid] = {}
        if pid not in calendar_result:
            calendar_result[pid] = {}
        for i, doc_id in enumerate(LANG_LIST):
            title = str(info_tbl.cell(r_idx, 2+i).value)
            content = str(info_tbl.cell(r_idx, 2+i+DOC_NUM).value)
            info = str(info_tbl.cell(r_idx, 2+i+DOC_NUM*2).value)
            calendar_title = str(info_tbl.cell(r_idx, 2 + i + DOC_NUM * 3).value)
            calendar_info = str(info_tbl.cell(r_idx, 2 + i + DOC_NUM * 4).value)
            if doc_id not in excel_result[pid]:
                excel_result[pid][doc_id] = {}
            if doc_id not in calendar_result[pid]:
                calendar_result[pid][doc_id] = {}
            excel_result[pid][doc_id]['title'] = title
            excel_result[pid][doc_id]['content'] = content
            excel_result[pid][doc_id]['info'] = info
            calendar_result[pid][doc_id]['calendar_title'] = calendar_title
            calendar_result[pid][doc_id]['calendar_info'] = calendar_info
    query = db.query.all()
    for que in query:
        project = str(que.project)
        if project in excel_result:
            localization = excel_result[project]
            calendar_localization = calendar_result[project]
            que.localization = json.dumps(localization)
            event_calendar = json.loads(que.event_calendar)
            event_calendar["localization"] = calendar_localization
            que.event_calendar = json.dumps(event_calendar)


def new_personal_event_project_v2_doc(excel_data, db):
    excel_result = {}
    info_tbl = excel_data.sheet_by_name("info")
    for r_idx in range(1, info_tbl.nrows):
        pid = str(int(info_tbl.cell(r_idx, 1).value))
        if pid not in excel_result:
            excel_result[pid] = {}
        for i, doc_id in enumerate(LANG_LIST):
            title = str(info_tbl.cell(r_idx, 2+i).value)
            info = str(info_tbl.cell(r_idx, 2+i+DOC_NUM).value)
            content = str(info_tbl.cell(r_idx, 2+i+DOC_NUM*2).value)
            help_info = str(info_tbl.cell(r_idx, 2 + i + DOC_NUM * 3).value)
            if doc_id not in excel_result[pid]:
                excel_result[pid][doc_id] = {}
            excel_result[pid][doc_id]['title'] = title
            excel_result[pid][doc_id]['info'] = info
            excel_result[pid][doc_id]['content'] = content
            excel_result[pid][doc_id]['help_info'] = help_info
    query = db.query.all()
    for que in query:
        project = str(que.project)
        if project in excel_result:
            localization = excel_result[project]
            que.localization = json.dumps(localization)


def strongest_sheriff_project_v2_doc(excel_data, db1, db2):
    excel_result = {}
    info_tbl = excel_data.sheet_by_name("main")
    for r_idx in range(1, info_tbl.nrows):
        pid = str(int(info_tbl.cell(r_idx, 1).value))
        if pid not in excel_result:
            excel_result[pid] = {}
        for i, doc_id in enumerate(LANG_LIST):
            title = str(info_tbl.cell(r_idx, 2+i).value)
            info = str(info_tbl.cell(r_idx, 2+i+DOC_NUM).value)
            content = str(info_tbl.cell(r_idx, 2+i+DOC_NUM*2).value)
            help_info = str(info_tbl.cell(r_idx, 2 + i + DOC_NUM * 3).value)
            if doc_id not in excel_result[pid]:
                excel_result[pid][doc_id] = {}
            excel_result[pid][doc_id]['title'] = title
            excel_result[pid][doc_id]['info'] = info
            excel_result[pid][doc_id]['content'] = content
            excel_result[pid][doc_id]['help_info'] = help_info
    query = db1.query.all()
    for que in query:
        project = str(que.project)
        if project in excel_result:
            localization = excel_result[project]
            que.localization = json.dumps(localization)

    excel_result = {}
    info_tbl = excel_data.sheet_by_name("sub")
    for r_idx in range(1, info_tbl.nrows):
        pid = str(int(info_tbl.cell(r_idx, 2).value))
        if pid not in excel_result:
            excel_result[pid] = {}
        for i, doc_id in enumerate(LANG_LIST):
            title = str(info_tbl.cell(r_idx, 3 + i).value)
            help_info = str(info_tbl.cell(r_idx, 3 + i + DOC_NUM).value)
            if doc_id not in excel_result[pid]:
                excel_result[pid][doc_id] = {}
            excel_result[pid][doc_id]['title'] = title
            excel_result[pid][doc_id]['info'] = 'info'
            excel_result[pid][doc_id]['content'] = 'content'
            excel_result[pid][doc_id]['help_info'] = help_info
    query = db2.query.all()
    for que in query:
        project = str(que.project)
        if project in excel_result:
            localization = excel_result[project]
            que.localization = json.dumps(localization)


def old_al_legion_event_project_doc(excel_data, db):
    excel_result = {}
    info_tbl = excel_data.sheet_by_name("info")
    for r_idx in range(1, info_tbl.nrows):
        pid = str(int(info_tbl.cell(r_idx, 1).value))
        if pid not in excel_result:
            excel_result[pid] = {}
        for i, doc_id in enumerate(LANG_LIST):
            title = str(info_tbl.cell(r_idx, 2 + i).value)
            info = str(info_tbl.cell(r_idx, 2 + i + DOC_NUM).value)
            content = str(info_tbl.cell(r_idx, 2 + i + DOC_NUM * 2).value)
            if doc_id not in excel_result[pid]:
                excel_result[pid][doc_id] = {}
            excel_result[pid][doc_id]['title'] = title
            excel_result[pid][doc_id]['info'] = info
            excel_result[pid][doc_id]['content'] = content
    query = db.query.all()
    for que in query:
        project = str(que.project)
        if project in excel_result:
            localization = excel_result[project]
            que.localization = json.dumps(localization)


def theme_event_project_doc(excel_data, db):
    excel_result = {}
    info_tbl = excel_data.sheet_by_name("info")
    for r_idx in range(1, info_tbl.nrows):
        pid = str(int(info_tbl.cell(r_idx, 1).value))
        if pid not in excel_result:
            excel_result[pid] = {}
        for i, doc_id in enumerate(LANG_LIST):
            title = str(info_tbl.cell(r_idx, 2 + i).value)
            info = str(info_tbl.cell(r_idx, 2 + i + DOC_NUM).value)
            content = str(info_tbl.cell(r_idx, 2 + i + DOC_NUM * 2).value)
            if doc_id not in excel_result[pid]:
                excel_result[pid][doc_id] = {}
            excel_result[pid][doc_id]['title'] = title
            excel_result[pid][doc_id]['info'] = info
            excel_result[pid][doc_id]['content'] = content
    query = db.query.all()
    for que in query:
        project = str(que.project)
        if project in excel_result:
            localization = excel_result[project]
            que.localization = json.dumps(localization)


def shama_event_project_doc(excel_data, db):
    excel_result = {}
    info_tbl = excel_data.sheet_by_name("info")
    for r_idx in range(1, info_tbl.nrows):
        pid = str(int(info_tbl.cell(r_idx, 1).value))
        if pid not in excel_result:
            excel_result[pid] = {}
        for i, doc_id in enumerate(LANG_LIST):
            title = str(info_tbl.cell(r_idx, 2 + i).value)
            info = str(info_tbl.cell(r_idx, 2 + i + DOC_NUM).value)
            content = str(info_tbl.cell(r_idx, 2 + i + DOC_NUM * 2).value)
            if doc_id not in excel_result[pid]:
                excel_result[pid][doc_id] = {}
            excel_result[pid][doc_id]['title'] = title
            excel_result[pid][doc_id]['info'] = info
            excel_result[pid][doc_id]['content'] = content
    query = db.query.all()
    for que in query:
        project = str(que.project)
        if project in excel_result:
            localization = excel_result[project]
            que.localization = json.dumps(localization)


def bandit_event_project_doc(excel_data, db):
    excel_result = {}
    info_tbl = excel_data.sheet_by_name("info")
    for r_idx in range(1, info_tbl.nrows):
        pid = str(int(info_tbl.cell(r_idx, 1).value))
        if pid not in excel_result:
            excel_result[pid] = {}
        for i, doc_id in enumerate(LANG_LIST):
            title = str(info_tbl.cell(r_idx, 2 + i).value)
            info = str(info_tbl.cell(r_idx, 2 + i + DOC_NUM).value)
            content = str(info_tbl.cell(r_idx, 2 + i + DOC_NUM * 2).value)
            if doc_id not in excel_result[pid]:
                excel_result[pid][doc_id] = {}
            excel_result[pid][doc_id]['title'] = title
            excel_result[pid][doc_id]['info'] = info
            excel_result[pid][doc_id]['content'] = content
    query = db.query.all()
    for que in query:
        project = str(que.project)
        if project in excel_result:
            localization = excel_result[project]
            que.localization = json.dumps(localization)

def new_wg_lucky_wheel_doc(excel_data, db):
    excel_result = {}
    info_tbl = excel_data.sheet_by_name("info")
    for r_idx in range(1, info_tbl.nrows):
        pid = str(int(info_tbl.cell(r_idx, 1).value))
        if pid not in excel_result:
            excel_result[pid] = {
                "title": {},
                "info": {}
            }
        for i, doc_id in enumerate(LANG_LIST):
            title = str(info_tbl.cell(r_idx, 2 + i).value)
            info = str(info_tbl.cell(r_idx, 2 + i + DOC_NUM).value)
            if doc_id not in excel_result[pid]:
                excel_result[pid][doc_id] = {}
            excel_result[pid]['title'][doc_id] = title
            excel_result[pid]['info'][doc_id] = info
    query = db.query.all()
    for que in query:
        project = str(que.project)
        if project in excel_result:
            title_doc = excel_result[project]['title']
            info_doc = excel_result[project]['info']

            basic = json.loads(que.basic)
            info = json.loads(que.info)


            basic['title'] = title_doc
            info['info'] = info_doc

        que.basic = json.dumps(basic)
        que.info = json.dumps(info)

